Introduction:
Magento database is based on EAV(Entity Attribute Value) architecture. And it uses this architecture, especially for categories, products, customers & customer addresses.
Before Magento version 1.4.x it used EAV structure for orders as well but thereafter order’s EAV structure has been converted to a flat structure.
From the definition of EAV:
Entity(E):
Entity actually refers to the data item.
In Magento:
- catalog_category_entity
- catalog_product_entity
- customer_entity
- customer_address_entity
are the entity tables for categories, products, customers & customer addresses respectively.
Attribute(A):
Attribute refers to the different attributes of an Entity.
In Magento, attributes related to an entity are stored in a single table: eav_attribute but differentiated by field: entity_type_id.
We can easily find all the attributes related to an entity using the following SQL query(For Example product):
SELECT
attribute_code,
attribute_id,
backend_type
FROM
eav_attribute
WHERE entity_type_id =
(SELECT
entity_type_id
FROM
eav_entity_type
WHERE entity_type_code = 'catalog_product')Which gives the following results:
+----------------------------+--------------+--------------+
| attribute_code | attribute_id | backend_type |
+----------------------------+--------------+--------------+
| activation_information | 496 | text |
| color | 272 | int |
| color_code | 950 | varchar |
| computer_manufacturers | 510 | int |
| contrast_ratio | 875 | int |
| cost | 100 | decimal |
| country_orgin | 507 | text |
| cpu_speed | 877 | int |
| created_at | 930 | static |
| custom_design | 571 | varchar |
| custom_design_from | 572 | datetime |
| custom_design_to | 573 | datetime |
| custom_layout_update | 531 | text |
| description | 97 | text |
| dimension | 494 | text |
| enable_googlecheckout | 903 | int |
| finish | 509 | text |
| gallery | 271 | varchar |
| gender | 501 | int |
| gift_message_available | 562 | varchar |
| harddrive_speed | 878 | varchar |
| hardrive | 499 | text |
| has_options | 838 | static |
| image | 106 | varchar |
| image_label | 879 | varchar |
| in_depth | 492 | text |
| is_imported | 949 | int |
| is_recurring | 933 | int |
| links_exist | 947 | int |
| links_purchased_separately | 904 | int |
| links_title | 906 | varchar |
| manufacturer | 102 | int |
| max_resolution | 873 | varchar |
| media_gallery | 703 | varchar |
| megapixels | 513 | int |
| memory | 498 | text |
| meta_description | 105 | varchar |
| meta_keyword | 104 | text |
| meta_title | 103 | varchar |
| minimal_price | 503 | decimal |
| model | 495 | text |
| name | 96 | varchar |
| news_from_date | 704 | datetime |
| news_to_date | 705 | datetime |
| old_id | 110 | int |
| options_container | 836 | varchar |
| package_id | 951 | int |
| page_layout | 929 | varchar |
| price | 99 | decimal |
| price_type | 859 | int |
| price_view | 862 | int |
| processor | 497 | text |
| ram_size | 874 | varchar |
| recurring_profile | 934 | text |
| required_options | 837 | static |
| response_time | 876 | varchar |
| room | 508 | int |
| samples_title | 905 | varchar |
| screensize | 500 | text |
| shape | 476 | text |
| shipment_type | 863 | int |
| shipping_qty | 952 | int |
| shirt_size | 525 | int |
| shoe_size | 502 | int |
| shoe_type | 107 | int |
| short_description | 506 | text |
| sku | 98 | static |
| sku_type | 860 | int |
| small_image | 109 | varchar |
| small_image_label | 880 | varchar |
| special_from_date | 568 | datetime |
| special_price | 567 | decimal |
| special_to_date | 569 | datetime |
| status | 273 | int |
| tax_class_id | 274 | int |
| thumbnail | 493 | varchar |
| thumbnail_label | 881 | varchar |
| tier_price | 270 | decimal |
| updated_at | 931 | static |
| url_key | 481 | varchar |
| url_path | 570 | varchar |
| visibility | 526 | int |
| weight | 101 | decimal |
| weight_type | 861 | int |
+----------------------------+--------------+--------------+Similarly, you can find attributes for other entities just by using the following values for entity_type_code:
- catalog_category
- customer
- customer_address
for categories, customers & customer address respectively.
Note: You can get the related values for entity_type_code from table: eav_entity_type.
Value(V):
Value refers to the actual value of the attribute of the entity.
In Magento attribute values of an entity (for example: product) are stored in catalog_product_entity_{backend_type} tables.
Where {backend_type} refers to the value of field: backend_type (except value ‘static’) of table: eav_attribute.
Following SQL is used to find all the backend types related to a product entity:
SELECT DISTINCT
backend_type
FROM
eav_attribute
WHERE entity_type_id =
(SELECT
entity_type_id
FROM
eav_entity_type
WHERE entity_type_code = 'catalog_product')Which results:
+--------------+
| backend_type |
+--------------+
| text |
| int |
| varchar |
| decimal |
| static |
| datetime |
+--------------+So tables:
- catalog_product_entity_text
- catalog_product_entity_int
- catalog_product_entity_varchar
- catalog_product_entity_decimal
- catalog_product_entity_datetime
are used for storing values for an attribute of related backend_type.
Note: You must be wondering about backend_type = static. For an attribute with the static backend_type, values are stored as a column directly in the entity table. For example, sku is an attribute of backend_type = static and its values are stored in the entity table itself: catalog_product_entity under field: sku.
That’s all about the definition of EAV relating to Magento.
Hopefully, now you are able to differentiate which attribute values go where (in which table) based on the backend_type.
Thanks for reading & Happy E-Commerce!!